## 4.6. Utility Methods and Functions

To round off our basic introduction to DBI, we'll tell you about some useful utility methods and functions that will make your life that little bit easier. These include the very useful quote escaping method, DBI execution tracing, and various functions to tidy up your data.

### 4.6.1. Database-Specific Quote Handling

By far the most important utility method is `quote()` , which correctly quotes and escapes SQL statements in a way that is suitable for a given database engine. This feature is important if you have a Perl string that you wish to insert into a database, as the data will be required, in most cases, to have quotation marks around it.

To confuse matters, database engines tend to have a different format for specifying these surrounding quotation marks. DBI circumvents this problem by declaring the `quote()` method to be executed against a database handle, which ensures that the correct quotation rules are applied.

This method, when executed against a database handle, converts the string given as an argument according to defined rules, and returns the correctly escaped string for use against the database.

For example:

>     #!/usr/bin/perl -w
>     #
>     # ch04/util/quote1: Demonstrates the use of the $dbh->quote() method

>     use DBI;

>     ### The string to quote
>     my $string = "Don't view in monochrome (it looks 'fuzzy')!";

>     ### Connect to the database
>     my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" , {
>         RaiseError => 1
>     } );

>     ### Escape the string quotes ...
>     my $quotedString = $dbh->quote( $string );

>     ### Use quoted string as a string literal in a SQL statement
>     my $sth = $dbh->prepare( "
>         SELECT *
>         FROM media
>         WHERE description = $quotedString
>       " );
>     $sth->execute();

>     exit;

For example, if you quoted the Perl string of `Do` `it!` via an Oracle database handle, you would be returned the value of `'Do` `it!'`. However, the `quote()` method also takes care of cases such as `Don't` `do` `it!` which needs to be translated to `'Don''t` `do` `it!'` for most databases. The simplistic addition of surrounding quotes would have produced `'Don't` `do` `it!'` which is not a valid SQL string literal.

Some databases require a more complex `quote()` method, and some drivers (though not all) have a `quote()` method that can cope with multiline strings and even binary data.

As a special case, if the argument is `undef`, the `quote()` method returns the string `NULL` , without quotes. This corresponds to the DBI's use of `undef` to represent `NULL` values, and to how `NULL` values are used in SQL.

### 4.6.2. Tracing DBI Execution

DBI sports an extremely useful ability to generate runtime tracing information of what it's doing, which can be a huge time-saver when trying to track down strange problems in your DBI programs.

At the highest level, you can call the `DBI-` >`trace()` method, which enables tracing on all DBI operations from that point onwards. There are several valid tracing levels:

0

  Disables tracing.

1

  Traces DBI method execution showing returned values and errors.

2

  As for 1, but also includes method entry with parameters.

3

  As for 2, but also includes more internal driver trace information.

4

  Levels 4, and above can include more detail than is helpful.

The `trace()` method can be used with two argument forms, either specifying only the trace level or specifying both the trace level and a file to which the trace information is appended. The following example shows the use of `DBI-`>`trace()`:

>     #!/usr/bin/perl -w
>     #
>     # ch04/util/trace1: Demonstrates the use of DBI tracing.

>     use DBI;

>     ### Remove any old trace files
>     unlink 'dbitrace.log' if -e 'dbitrace.log';

>     ### Connect to a database
>     my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" );

>     ### Set the tracing level to 1 and prepare()
>     DBI->trace( 1 );
>     doPrepare();

>     ### Set trace output to a file at level 2 and prepare()
>     DBI->trace( 2, 'dbitrace.log' );
>     doPrepare();

>     ### Set the trace output back to STDERR at level 2 and prepare()
>     DBI->trace( 2, undef );
>     doPrepare();

>     exit;

>     ### prepare a statement (invalid to demonstrate tracing)
>     sub doPrepare {
>         print "Preparing and executing statement\n";
>         my $sth = $dbh->prepare( "
>             SELECT * FROM megalith
>         " );
>         $sth->execute();
>         return;
>     }

>     exit;

This program generates quite a bit of trace information, of which we'll show just a small fragment:

>     -> prepare for DBD::Oracle::db (DBI::db=HASH(0xcd45c)~0xcd4a4 '
>           SELECT * FROM megalith
>       ') thr0
>       <- prepare= DBI::st=HASH(0xcd648) at trace1 line 30.
>       -> execute for DBD::Oracle::st (DBI::st=HASH(0xcd648)~0x16afec) thr0
>       dbd_st_execute SELECT (out0, lob0)...
>       !! ERROR: 942 'ORA-00942: table or view does not exist (DBD ERROR:
>          OCIStmtExecute)'
>       <- execute= undef at trace1 line 33.
>     DBD::Oracle::st execute failed: ORA-00942: table or view does not exist (DBD
>     ERROR: OCIStmtExecute) at trace1 line 33.

This trace information was generated with a setting of level 2, and shows the operations that DBI undertook when trying to prepare and execute a statement. Lines prepended with `-`> are written when the method is being entered, and lines prepended with <`-` are written when the method is returning. These lines also show the information being returned from the method call. The DBI trace output is indented by four spaces to make it easier to distinguish the trace output from any other program output.

You can see the `prepare()` method being called along with its parameters: a database handle and the SQL statement to prepare.[42] The next line shows the `prepare()` returning a statement handle. It also shows the file and line number that `prepare()` was called from. Following that, we see `execute()` being called, a trace line from the driver itself, and the method returning after logging an error. Finally we see the warning generated by the DBI due to the `PrintError` attribute, which is on by default.

> [42]If the Perl you are using was built with threading enabled, then each method entry line also shows the thread number, e.g., `thr0`. The DBI implements a per-driver mutex so that each DBD driver may only be entered by one thread at a time. Trace levels 4 and above show this in action.

The trace information generated at level 1 is similar. The main difference is that the method entry lines (`-`>) are not shown.

The one drawback to this form of tracing is that if your program uses a lot of handles, then the volume of tracing information could be quite vast. Similarly, you might have tracked your problem down to a specific database operation that you'd like to trace individually.

The `trace()` method is also available at a handle level, allowing you to individually trace any database and statement handle operations. Therefore, you could trace operations on a given database handle to level 1 and a single statement handle to level 2. For example:

>     ### Connect to a database...
>     my $dbh = DBI->connect( "dbi:Oracle:archaeo", "username", "password" );

>     ### Trace the database handle to level 1 to the screen
>     $dbh->trace( 1 );

>     ### Create a new statement
>     my $sth = ...;

>     ### Trace the statement to level 2 to the file 'trace.lis'
>     $sth->trace( 2, 'trace.lis' );

Note that if a filename is specified when calling `trace()`, then currently, trace output from all handles is redirected to that file.

If your programs are exhibiting odd behavior or are generating errors on a regular basis, you should consider using the built-in tracing features of DBI to help you resolve your problems. This tool is extremely useful, as you will be able to see exactly what data is being passed to the database, allowing you to ensure that it's in the correct format.

Finally, tracing can also be controlled via the use of an environment variable called `DBI_TRACE` , which acts in a similar manner to the `DBI-`>`trace()` method. That is, it traces all handles used within the program. This environment variable can be used in three ways that are summarized in the following table.

DBI_TRACE Value | Effect on DBI
--- | ---
>     1
>     DBI->trace(1);
>     dbitrace.log
>     DBI->trace(2, 'dbitrace.log');
>     4=dbitrace.log
>     DBI->trace(4, 'dbitrace.log');

If the trace level isn't specified in the `DBI_TRACE` environment variable, it will default to `2`, as shown in the table above.

### 4.6.3. Neat and Tidy Formatting

The DBI features a couple of utility functions that can be used to tidy up strings into a form suitable for easy reading. These two functions are `neat()` and `neat_list()` , the former operating on a single scalar value, the latter operating on a list of scalar values.

For example, to use `neat()` to tidy up some strings, you could write:

>     #!/usr/bin/perl -w
>     #
>     # ch04/util/neat1: Tests out the DBI::neat() utility function.
>     #

>     use DBI;

>     ### Declare some strings to neatify
>     my $str1 = "Alligator's an extremely neat() and tidy person";
>     my $str2 = "Oh no\nhe's not!";

>     ### Neatify this first string to a maxlen of 40
>     print "String: " . DBI::neat( $str1, 40 ) . "\n";

>     ### Neatify the second string to a default maxlen of 400
>     print "String: " . DBI::neat( $str2 ) . "\n";

>     ### Neatify a number
>     print "Number: " . DBI::neat( 42 * 9 ) . "\n";

>     ### Neatify an undef
>     print "Undef: " . DBI::neat( undef ) . "\n";

>     exit;

which generates the output of:

>     String: 'Alligator's an extremely neat() and...'
>     String: 'Oh no
>     he's not!'
>     Number: 378
>     Undef:  undef

demonstrating that string values are quoted,[43] whereas values known to be numeric are not. The first string has been truncated to the desired length with `...` added. Undefined values are recognized and returned as the string `undef` without quotes.

> [43]Note that internal quotes are not escaped. That's because `neat()` is designed to produce output for human readers, and to do so quickly since it's used by the internal trace mechanisms. If you wish quote escaping to occur, you could use the `quote()` method instead.

While the `neat()` function is handy for single values, the `neat_list()` function is handy for lists. It simply calls `neat()` on each element of the referenced list before joining the list of values together with the desired separator string. For example:

>     #!/usr/bin/perl -w
>     #
>     # ch04/util/neat2: Tests out the DBI::neat_list() utility function

>     use DBI qw( neat_list );

>     ### Declare some strings to neatify
>     my @list = ( 'String-a-string-a-string-a-string-a-string', 42, 0, '', undef );

>     ### Neatify the strings into an array
>     print neat_list( \@list, 40, ", " ), "\n";

>     exit;

which generates the output of:

>     'String-a-string-a-string-a-string-a...', 42, 0, '', undef

This example also shows that the utility functions can be imported into your package so you can drop the `DBI::` prefix.

DBI uses `neat()` and `neat_list()` internally to format the output generated by tracing. That's important to know if you're wondering why the trace output is truncating your huge SQL statements down to 400 characters.[44]

> [44]400 characters is the default value for the `$DBI::neat_maxlen` variable, which defines the default maximum length for the `neat()` function.

### 4.6.4. Numeric Testing

The final utility function supplied by DBI that we'll look at is quite a curious one called `looks_like_number()` . This function quite simply tells you whether or not a value looks like a number or not.

`looks_like_number()` operates by taking a list of values as an argument and returns a new array signifying whether or not the corresponding value within the original array was a number, not a number, or undefined.

This may seem rather a curious thing to want to do, but in the case of handling large quantities of data, it's useful for working out which values might need to have their quotes escaped via the `quote()` method.

The returned array will contain the same number of values as the original data array, with the elements containing one of three values signifying the following:

>     true        The original value is a number.
>     false       The original value is not a number.
>     undef       The original value is empty or undefined.

The following example illustrates how this process works:

>     #!/usr/bin/perl -w
>     #
>     # ch04/util/lookslike1: Tests out the DBI::looks_like_number() function.
>     #

>     use DBI;

>     ### Declare a list of values
>     my @values = ( 333, 'Choronzon', 'Tim', undef, 'Alligator', 1234.34,
>                    'Linda', 0x0F, '0x0F', 'Larry Wall' );

>     ### Check to see which are numbers!
>     my @areNumbers = DBI::looks_like_number( @values );

>     for (my $i = 0; $i < @values; ++$i ) {

>         my $value = (defined $values[$i]) ? $values[$i] : "undef";

>         print "values[$i] -> $value ";

>         if ( defined $areNumbers[$i] ) {
>             if ( $areNumbers[$i] ) {
>                 print "is a number!\n";
>             }
>             else {
>                 print "is utterly unlike a number and should be quoted!\n";
>             }
>         }
>         else {
>             print "is undefined!\n";
>         }
>     }

>     exit;

The results from this program illustrate how the values are treated and shows that hexadecimal values are not treated as numbers:

>     values[0] -> 333 is a number!
>     values[1] -> Choronzon is utterly unlike a number and should be quoted!
>     values[2] -> Tim is utterly unlike a number and should be quoted!
>     values[3] -> undef is undefined!
>     values[4] -> Alligator is utterly unlike a number and should be quoted!
>     values[5] -> 1234.34 is a number!
>     values[6] -> Linda is utterly unlike a number and should be quoted!
>     values[7] -> 15 is a number!
>     values[8] -> 0x0F is utterly unlike a number and should be quoted!
>     values[9] -> Larry Wall is utterly unlike a number and should be quoted!

The first `0x0F` in the list of values is reported as looking like a number because Perl converted it into one (`15`) when the script was compiled. The second is not reported as looking like a number because the `looks_like_number()` function only looks for integers and floating-point numbers.

And that brings us to the end of the introduction to DBI and its architecture. We'll be talking more on how to actually do stuff with DBI in the next chapter.

Copyright © 2001 O'Reilly & Associates. All rights reserved.